﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.InteropServices;
using NHibernate;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;

namespace DunSilverlight.Web.Comm
{
    /// <summary>
    /// 数据导入工具类
    ///  owen 2011-11-29
    /// </summary>
    public class DataImporter
    {
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

        /// <summary>
        private const string Original_ArrearRecord_SheetName = "欠款信息导入模板";

        private const string Original_ReturnRecord_SheetName = "还款信息导入模板";

        /// 欠费原始记录表
        /// </summary>
        private const string Original_ArrearRecord_TableName = "Original_ArrearRecord";
        /// <summary>
        /// 回款原始记录
        /// </summary>
        private const string Original_ReturnRecord_TableName = "Original_ReturnRecord";
        /// <summary>
        /// 首先将excel数据原封不动导入到数据库的【Original_ArrearRecord】
        /// 后面再做业务导入，将数据拆分到各个业务表：持卡人，欠费记录，联系记录，联系人等
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="bankId">银行id</param>
        /// <param name="period">期间</param>
        /// <param name="sess">数据库Session</param>
        /// <param name="copiedCallBack">回调函数</param>
        public void ImportOriginalArrearRecord(string fileName, int bankId, int year, int period, int branchCompanyId, int userId, ISession sess, SqlRowsCopiedEventHandler copiedCallBack)
        {
            try
            {
                TransferArrearRecord(fileName, Original_ArrearRecord_SheetName, bankId, year, period, branchCompanyId, sess.Connection, copiedCallBack);

                ArrearBizImport(bankId, year, period, branchCompanyId,userId, sess.Connection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ImportOriginalReturnRecord(string fileName, int bankId, int branchCompanyId,int userId, ISession sess, SqlRowsCopiedEventHandler copiedCallBack)
        {
            try
            {
                TransferReturnRecord(fileName, Original_ReturnRecord_SheetName, bankId, branchCompanyId, sess.Connection, copiedCallBack);

                ReturnBizImport(bankId, branchCompanyId,userId,  sess.Connection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 欠款信息导入到临时表
        /// </summary>
        /// <param name="excelFile"></param>
        /// <param name="sheetName"></param>
        /// <param name="bankId"></param>
        /// <param name="year"></param>
        /// <param name="period"></param>
        /// <param name="branchCompanyId"></param>
        /// <param name="conn"></param>
        /// <param name="copiedCallBack"></param>
        private void TransferArrearRecord(string excelFile, string sheetName, int bankId, int year, int period, int branchCompanyId, IDbConnection conn, SqlRowsCopiedEventHandler copiedCallBack)
        {

            DataSet ds = new DataSet();
            IDbConnection xConn = null;

            try
            {

                string fullPath = AppDomain.CurrentDomain.BaseDirectory + excelFile;

                //删除该银行当期的所有数据
                IDbCommand cmd = conn.CreateCommand();

                cmd.CommandText = string.Format("delete from Original_ArrearRecord where bankid={0} and year={1} and period={2}", bankId, year, period);

                cmd.ExecuteNonQuery();

                //FileStream file = File.Open(excelFile,FileMode.Open,FileAccess.Read);

                //获取全部数据
                string strConn = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1';", fullPath, "Yes");

                xConn = new OleDbConnection(strConn);

                xConn.Open();

                string strExcel = "";

                OleDbDataAdapter myCommand = null;

                strExcel = string.Format(
                    @"select
                        CInt({0})                   as BankId,
                        CInt({1})                   as [Year],
                        CInt({2})                   as [Period],
                        CInt({3})                   as BranchCompanyId,
                        Trim(分行名称)              as BranchName,
                        Trim(支行名称)              as SubBranchName,                      
                        Trim(姓名)                  as HolderName,
                        Trim(证件号码)              as IdCardNo,
                        Trim(卡号)                  as CardNo,  
                        Trim(账号)                  as AccountNo,
                        Trim(客户迟缴级别)          as HolderDelayLevel,
                        Trim(账户迟缴级别)          as CardDelayLevel,
                        Trim(开卡日期)              as OpenCardDateTime,
                        Trim(主副卡)                as MainAddSymbol,
                        Trim(最后发单日期)          as LastBillDateTime,
                        Trim(逾期)                  as Overdue,
                        Trim(授信额度)              as Quota,
                        Trim(本金)                  as Capital,
                        Trim(利息)                  as Interest,
                        Trim(滞纳金)                as LateFee,
                        Trim(超限费)                as AboveQuota,
                        Trim(服务费)                as ServiceCharge,
                        Trim(欠款金额)              as Overdraft,
                        Trim(币种)                  as CurrencyName,
                        CStr(1)                     as Exchange,
                        Trim(手机号码)              as Mobile,
                        Trim(地区)                  as District,
                        Trim(家庭住址)              as HomeAddress,
                        Trim(家庭邮编)              as HomePost,
                        Trim(家庭电话)              as HomeTelephone,
                        Trim(单位名称)              as OrganizationName,
                        Trim(单位地址)              as OrganizationAddress,
                        Trim(单位邮编)              as OrganizationPost,
                        Trim(单位电话)              as OrganizationTelephone,
                        Trim(电子邮箱)              as EMail,
                        Trim(联系人姓名1)           as ContactName1,
                        Trim(联系人电话1)           as ContactTelephone1, 
                        Trim(关系1)                 as Relation1,  
                        Trim(联系人姓名2)           as ContactName2,
                        Trim(联系人电话2)           as ContactTelephone2, 
                        Trim(关系2)                 as Relation2,
                        Trim(联系人姓名3)           as ContactName3,
                        Trim(联系人电话3)           as ContactTelephone3, 
                        Trim(关系3)                 as Relation3,
                        Trim(账单日)                as BillDate,
                        Trim(卡类型)                as CardTypeName
                     from [{4}$]", bankId, year, period, branchCompanyId, sheetName);

                myCommand = new OleDbDataAdapter(strExcel, strConn);

                myCommand.Fill(ds, sheetName);

                using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy((SqlConnection)conn))
                {
                    if (copiedCallBack != null)
                        bcp.SqlRowsCopied += copiedCallBack;

                    bcp.BatchSize = 100;//每次传输的行数

                    bcp.NotifyAfter = 100;//进度提示的行数

                    bcp.DestinationTableName = Original_ArrearRecord_TableName;//目标表

                    bcp.WriteToServer(ds.Tables[0]);
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (xConn != null)
                    xConn.Close();
            }
        }
        /// <summary>
        /// 欠费业务导入
        /// </summary>
        /// <param name="conn"></param>
        private int ArrearBizImport(int bankId, int year, int period, int branchCompanyId, int userId,IDbConnection conn)
        {
            IDbCommand cmd = conn.CreateCommand();

            cmd.CommandText = string.Format("exec Import_ArrearRecord {0},{1},{2},{3},{4}", bankId, year, period, branchCompanyId,userId);
            int result = cmd.ExecuteNonQuery();
            return result;
        }


        /// <summary>
        /// 回款信息导入到临时表
        /// </summary>
        /// <param name="excelFile"></param>
        /// <param name="sheetName"></param>
        /// <param name="bankId"></param>
        /// <param name="year"></param>
        /// <param name="period"></param>
        /// <param name="branchCompanyId"></param>
        /// <param name="conn"></param>
        /// <param name="copiedCallBack"></param>
        private void TransferReturnRecord(string excelFile, string sheetName, int bankId, int branchCompanyId, IDbConnection conn, SqlRowsCopiedEventHandler copiedCallBack)
        {

            DataSet ds = new DataSet();
            IDbConnection xConn = null;
            try
            {
                string fullPath = AppDomain.CurrentDomain.BaseDirectory + excelFile;

                //删除该银行当期的所有数据

                IDbCommand cmd = conn.CreateCommand();

                cmd.CommandText = string.Format("delete from Original_ReturnRecord ");

                cmd.ExecuteNonQuery();

                //获取全部数据
                string strConn = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1';", fullPath, "Yes");

                xConn = new OleDbConnection(strConn);

                xConn.Open();

                string strExcel = "";

                OleDbDataAdapter myCommand = null;

                strExcel = string.Format(
                    @"select
                        Trim(姓名)                  as HolderName,
                        Trim(证件号码)              as IdCardNo,
                        Trim(卡号)                  as CardNo,  
                        Trim(分行名称)              as BranchName,                      
                        Trim(回款金额)              as ReturnAmount,
                        Trim(回款日期)              as ReturnDate,
                        Trim(币种)                  as CurrencyName
                     from [{0}$]", sheetName);

                myCommand = new OleDbDataAdapter(strExcel, strConn);

                myCommand.Fill(ds, sheetName);

                using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy((SqlConnection)conn))
                {
                    if (copiedCallBack != null)
                        bcp.SqlRowsCopied += copiedCallBack;

                    bcp.BatchSize = 100;//每次传输的行数

                    bcp.NotifyAfter = 100;//进度提示的行数

                    bcp.DestinationTableName = Original_ReturnRecord_TableName;//目标表

                    bcp.WriteToServer(ds.Tables[0]);
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (xConn != null)
                    xConn.Close();
            }
        }

        /// <summary>
        /// 回款业务导入
        /// </summary>
        /// <param name="conn"></param>
        private int ReturnBizImport(int bankId, int branchCompanyId,int userId, IDbConnection conn)
        {
            IDbCommand cmd = conn.CreateCommand();

            cmd.CommandText = string.Format("exec Import_ReturnRecord {0},{1}",branchCompanyId,userId);
            int result = cmd.ExecuteNonQuery();
            return result;
        }

    }
}